﻿using System;
using System.ComponentModel;
using System.Data;
using System.Data.OracleClient;
using System.Web.Services;
using System.Xml;

namespace WS_Hudba
{
  /// <summary>
  /// Summary description for Service1
  /// </summary>
  [WebService(Namespace = "http://vecirek.char-t.com/xml/hudba/")]
  [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
  [ToolboxItem(false)]
  // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
  // [System.Web.Script.Services.ScriptService]
  public class Service1 : WebService
  {
    private static OracleConnection Connection;

    [WebMethod]
    public string HelloWorld()
    {
      return "\n[Hudba] Hello World!";
    }

    private static string GetConnectionString()
    {
      return "User ID=hejlt6am;Password=heslo;Unicode=True;Persist Security Info=True;" +
             "Data Source=(DESCRIPTION=" +
             "(ADDRESS=(PROTOCOL=TCP)(HOST=tirpitz.ms.mff.cuni.cz)(PORT=1511))" +
             "(CONNECT_DATA=(SID=xml)));"; // zmena portu z 1521, ten funguje jen z ms sítě
    }

    [WebMethod]
    public XmlElement getSuitableHudba(string styl)
    {
      return getFirmaFromDB(styl).DocumentElement;
    }

    /// <summary>
    /// Vrati XmlDocument obsahujuci <list><budova>...</budova>*</list>, nacitane z db
    /// </summary>
    public XmlDocument getFirmaFromDB(string styl)
    {
      string output;
      bool shouldDisconnect = !IsConnected;
      var doc = new XmlDocument();

      if (IsConnected || Connect())
      {
        try
        {
          OracleCommand command = Connection.CreateCommand();
          command.CommandType = CommandType.Text;
          command.CommandText = "SELECT ID, SYS.XMLTYPE.GETSTRINGVAL(KAPELA) FROM HUDBA WHERE existsNode(kapela, '/kapela/styly/styl=\"" + styl + "\"') = 1";

          OracleDataReader ord = command.ExecuteReader();
          output = "<list>";
          while (ord.Read())
          {
            output += string.Format("<record><id>{0}</id>{1}</record>", ord.GetInt32(0), ord.GetString(1));
          }
          output += "</list>";
          doc.LoadXml(output);
          return doc;
        }
        catch (Exception e)
        {
          return null;
        }
        finally
        {
          if (shouldDisconnect) Disconnect();
        }
      }
      return null;
    }

    [WebMethod]
    public XmlElement setPrice(int id, int price)
    {
      return setPriceDB(id, price).DocumentElement;
    }

    private XmlDocument setPriceDB(int id, int price)
    {
      string output;
      bool shouldDisconnect = !IsConnected;
      var doc = new XmlDocument();

      if (IsConnected || Connect())
      {
        try
        {
          OracleCommand command = Connection.CreateCommand();
          command.CommandType = CommandType.Text;
          command.CommandText = "UPDATE hudba SET kapela = updateXML(kapela, '//cena/text()', '" + price + "') WHERE id = " + id;
          command.ExecuteReader();
          output = "<update>ok</update>";
          doc.LoadXml(output);
          return doc;
        }
        catch (Exception e)
        {
          return null;
        }
        finally
        {
          if (shouldDisconnect) Disconnect();
        }
      }
      return null;
    }

    [WebMethod]
    public XmlElement newKapela(string kapela)
    {
      return newKapelaDB(kapela).DocumentElement;
    }

    private XmlDocument newKapelaDB(string kapela)
    {
      string output;
      bool shouldDisconnect = !IsConnected;
      var doc = new XmlDocument();

      if (IsConnected || Connect())
      {
        try
        {

          OracleCommand command_id = Connection.CreateCommand();
          command_id.CommandType = CommandType.Text;
          command_id.CommandText = "SELECT id FROM HUDBA ORDER BY id DESC";

          OracleDataReader ord = command_id.ExecuteReader();
          int id = 0;
          if (ord.Read())
          {
            id = ord.GetInt32(0) + 1;
          }

          OracleCommand command = Connection.CreateCommand();
          command.CommandType = CommandType.Text;
          command.CommandText = "INSERT INTO hudba (id, kapela) VALUES (" + id + ", XMLTYPE('" + kapela + "')) ";
          command.ExecuteReader();
          output = "<insert>ok</insert>";
          doc.LoadXml(output);
          return doc;
        }
        catch (Exception e)
        {
          output = "<exception>" + e.Message +"</exception>";
          doc.LoadXml(output);
          return doc;
        }
        finally
        {
          if (shouldDisconnect) Disconnect();
        }
      }
      output = "<error>disconnected</error>";
      doc.LoadXml(output);
      return doc;
    }

    #region connect/dc

    public static bool IsConnected
    {
      get
      {
        return (Connection != null) &&
               (Connection.State != ConnectionState.Broken) &&
               (Connection.State != ConnectionState.Closed);
      }
    }

    public static bool Connect()
    {
      Connection = new OracleConnection { ConnectionString = GetConnectionString() };

      try
      {
        Connection.Open();
        return true;
      }
      catch (Exception e)
      {
        return false;
      }
    }

    public static bool Disconnect()
    {
      if (Connection != null)
      {
        try
        {
          if ((Connection.State != ConnectionState.Closed) &&
              (Connection.State != ConnectionState.Broken))
          {
            Connection.Close();
          }

          Connection = null;
          return true;
        }
        catch (Exception e)
        {
          return false;
        }
      }
      return true;
    }

    #endregion
  }
}